namespace PMS.BLL
{
    using PMS.DBUtility;
    using PMS.Model;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Runtime.InteropServices;
    using System.Text;

    public class UserReportBLL
    {
        public int Add(UserReport model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("insert into UserReport(");
            builder.Append("AddDate,ReportName,IsOffice,ShowColumns,SelectSql,ShowOrganName,ShowUserName,ShowUserTel,ShowDate");
            builder.Append(") values (");
            builder.Append("@AddDate,@ReportName,@IsOffice,@ShowColumns,@SelectSql,@ShowOrganName,@ShowUserName,@ShowUserTel,@ShowDate");
            builder.Append(") ");
            builder.Append(";select @@IDENTITY");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@AddDate", model.AddDate), new SqlParameter("@ReportName", model.ReportName), new SqlParameter("@IsOffice", model.IsOffice), new SqlParameter("@ShowColumns", model.ShowColumns), new SqlParameter("@SelectSql", model.SelectSql), new SqlParameter("@ShowOrganName", model.ShowOrganName), new SqlParameter("@ShowUserName", model.ShowUserName), new SqlParameter("@ShowUserTel", model.ShowUserTel), new SqlParameter("@ShowDate", model.ShowDate) };
            object single = DBHelper.GetSingle(builder.ToString(), cmdParms);
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public bool Delete(int ReportID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from UserReport ");
            builder.Append(" where ReportID=@ReportID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@ReportID", SqlDbType.Int, 4) };
            cmdParms[0].Value = ReportID;
            return (DBHelper.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool DeleteList(string ReportIDlist)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from UserReport ");
            builder.Append(" where ID in (" + ReportIDlist + ")  ");
            return (DBHelper.ExecuteSql(builder.ToString()) > 0);
        }

        public List<UserReport> GetList(int IsOffice = 0)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select * ");
            builder.Append(" FROM UserReport ");
            if (IsOffice != 0)
            {
                builder.Append(" WHERE IsOffice=").Append(IsOffice);
            }
            DataTable table = DBHelper.Query(builder.ToString()).Tables[0];
            UserReport item = null;
            List<UserReport> list = null;
            if (table.Rows.Count > 0)
            {
                list = new List<UserReport>();
                foreach (DataRow row in table.Rows)
                {
                    item = new UserReport();
                    if (row["ReportID"].ToString() != "")
                    {
                        item.ReportID = int.Parse(row["ReportID"].ToString());
                    }
                    if (row["AddDate"].ToString() != "")
                    {
                        item.AddDate = DateTime.Parse(row["AddDate"].ToString());
                    }
                    item.ReportName = row["ReportName"].ToString();
                    if (row["IsOffice"].ToString() != "")
                    {
                        item.IsOffice = int.Parse(row["IsOffice"].ToString());
                    }
                    item.ShowColumns = row["ShowColumns"].ToString();
                    item.SelectSql = row["SelectSql"].ToString();
                    if (row["ShowOrganName"].ToString() != "")
                    {
                        item.ShowOrganName = int.Parse(row["ShowOrganName"].ToString());
                    }
                    if (row["ShowUserName"].ToString() != "")
                    {
                        item.ShowUserName = int.Parse(row["ShowUserName"].ToString());
                    }
                    if (row["ShowUserTel"].ToString() != "")
                    {
                        item.ShowUserTel = int.Parse(row["ShowUserTel"].ToString());
                    }
                    if (row["ShowDate"].ToString() != "")
                    {
                        item.ShowDate = int.Parse(row["ShowDate"].ToString());
                    }
                    list.Add(item);
                }
            }
            return list;
        }

        public UserReport GetModel(int ReportID)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ReportID, AddDate, ReportName, IsOffice, ShowColumns, SelectSql, ShowOrganName, ShowUserName, ShowUserTel, ShowDate  ");
            builder.Append("  from UserReport ");
            builder.Append(" where ReportID=@ReportID");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@ReportID", SqlDbType.Int, 4) };
            cmdParms[0].Value = ReportID;
            UserReport report = new UserReport();
            DataSet set = DBHelper.Query(builder.ToString(), cmdParms);
            if (set.Tables[0].Rows.Count > 0)
            {
                DataRow row = set.Tables[0].Rows[0];
                if (row["ReportID"].ToString() != "")
                {
                    report.ReportID = int.Parse(row["ReportID"].ToString());
                }
                if (row["AddDate"].ToString() != "")
                {
                    report.AddDate = DateTime.Parse(row["AddDate"].ToString());
                }
                report.ReportName = row["ReportName"].ToString();
                if (row["IsOffice"].ToString() != "")
                {
                    report.IsOffice = int.Parse(row["IsOffice"].ToString());
                }
                report.ShowColumns = row["ShowColumns"].ToString();
                report.SelectSql = row["SelectSql"].ToString();
                if (row["ShowOrganName"].ToString() != "")
                {
                    report.ShowOrganName = int.Parse(row["ShowOrganName"].ToString());
                }
                if (row["ShowUserName"].ToString() != "")
                {
                    report.ShowUserName = int.Parse(row["ShowUserName"].ToString());
                }
                if (row["ShowUserTel"].ToString() != "")
                {
                    report.ShowUserTel = int.Parse(row["ShowUserTel"].ToString());
                }
                if (row["ShowDate"].ToString() != "")
                {
                    report.ShowDate = int.Parse(row["ShowDate"].ToString());
                }
                return report;
            }
            return null;
        }

        public bool Update(UserReport model)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update UserReport set ");
            builder.Append(" AddDate = @AddDate , ");
            builder.Append(" ReportName = @ReportName , ");
            builder.Append(" IsOffice = @IsOffice , ");
            builder.Append(" ShowColumns = @ShowColumns , ");
            builder.Append(" SelectSql = @SelectSql , ");
            builder.Append(" ShowOrganName = @ShowOrganName , ");
            builder.Append(" ShowUserName = @ShowUserName , ");
            builder.Append(" ShowUserTel = @ShowUserTel , ");
            builder.Append(" ShowDate = @ShowDate  ");
            builder.Append(" where ReportID=@ReportID ");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@ReportID", model.ReportID), new SqlParameter("@AddDate", model.AddDate), new SqlParameter("@ReportName", model.ReportName), new SqlParameter("@IsOffice", model.IsOffice), new SqlParameter("@ShowColumns", model.ShowColumns), new SqlParameter("@SelectSql", model.SelectSql), new SqlParameter("@ShowOrganName", model.ShowOrganName), new SqlParameter("@ShowUserName", model.ShowUserName), new SqlParameter("@ShowUserTel", model.ShowUserTel), new SqlParameter("@ShowDate", model.ShowDate) };
            return (DBHelper.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }
    }
}

